<?php
class NHSChoicesDBContoller {
	public function NHSChoicesDBContoller() {
		global $wpdb;
		$this->wpdb = $wpdb;
		
		$this->TABLE_NAMES = array(
			'nhschoices' => $this->wpdb->prefix . 'nhschoices'
		);
	}
	
	public function install() {
		$table_name = $this->TABLE_NAMES['nhschoices'];
		if($this->wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
			$sql = "CREATE TABLE $table_name (
				id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
				guid VARCHAR(50) NOT NULL,
				name VARCHAR(50) NOT NULL,
				service INT(2) NOT NULL,
				address1 VARCHAR(50) NOT NULL,
				address2 VARCHAR(50),
				address3 VARCHAR(50),
				address4 VARCHAR(50),
				address5 VARCHAR(50),
				location VARCHAR(50),
				postcode VARCHAR(10) NOT NULL,
				telephone VARCHAR(20),
				website VARCHAR(255),
				latitude VARCHAR(50) NOT NULL,
				longitude VARCHAR(50) NOT NULL,
				distance VARCHAR(50) NOT NULL,
				url VARCHAR(255) NOT NULL,
				PRIMARY KEY id (id)
			); ";
			
			$this->wpdb->query($sql);
			
			$sql = "ALTER TABLE $table_name ADD UNIQUE (guid)";
			$this->wpdb->query($sql);
		}
	}
	
	public function get_results($service, $location, $rowcount = 10) {
		$table_name = $this->TABLE_NAMES['nhschoices'];
		$sql = "SELECT guid, name, service, address1, address2, address3, address4, address5, postcode, telephone, website, latitude, longitude, url, distance FROM $table_name WHERE location = '" . str_replace("'", "''", $location) . "' AND service = " . intval($service) . " ORDER BY distance LIMIT $rowcount";
		return $this->wpdb->get_results($sql, ARRAY_A);
	}
	
	public function clear_cache() {
		$table_name = $this->TABLE_NAMES['nhschoices'];
		$sql = "DELETE FROM $table_naem";
		return $this->wpdb->query($sql);
	}
	
	public function add_array($results, $location) {
		$table_name = $this->TABLE_NAMES['nhschoices'];
		$sqls = array();
		
		foreach($results as $result) {
			$statements = array();
			
			$result['location'] = $location;
			foreach($result as $key => $value) {
				$statement = "$key = '" . str_replace("'", "''", $value) . "'";
				$statements[] = $statement;
			}
			
			$statements = implode(', ', $statements);
			$sqls[] = "REPLACE INTO $table_name SET $statements";
		}
		
		foreach($sqls as $sql) {
			$this->wpdb->query($sql);
		}
	}
}
?>